﻿package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.Quest;

public class QuestDao {

	public QuestDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";

	// 특정 study내의 quest 리스트 불러오기
	public ArrayList<Quest> getAllQuestList(int studyId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Quest> questList = new ArrayList<Quest>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select questId, sid, quest.title, memid, quest.createdTime, quest.expiredTime from quest,user,study where studyid=sid and quest.uid=userid and sid=? order by questId desc");
			pstmt.setInt(1, studyId);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int questId = rs.getInt("questId");
				int sid = rs.getInt("sid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp expiredTime = rs.getTimestamp("expiredTime");
				questList.add(new Quest(questId, sid, title, memid,
						createdTime, expiredTime));
			}

			return questList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return questList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 마감된 퀘스트 체크
	public boolean checkExpiredQuest(int qid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select questId from quest where expiredTime<sysdate() and questId=?");
			pstmt.setInt(1, qid);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				return true;
			} else {
				return false;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// quest 내용 불러오기
	public Quest getQuestInfo(int questId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Quest quest = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select questId, sid, title, content, memid, createdTime, expiredTime from quest, user where uid=userid and questid=? ");
			pstmt.setInt(1, questId);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				int qid = rs.getInt("questId");
				int sid = rs.getInt("sid");
				String title = rs.getString("title").trim();
				String content = rs.getString("content").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp updatedTime = null;
				Timestamp expiredTime = rs.getTimestamp("expiredTime");
				quest = new Quest(qid, sid, title, content, memid, createdTime,
						updatedTime, expiredTime);
			}

			return quest;

		} catch (Exception ex) {
			ex.printStackTrace();
			return quest;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// quest 작성
	public void insertQuestInfo(Quest newQuest) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int qid = 0;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into quest(sid, title, content, uid, createdTime, expiredTime) values(?,?,?,?,sysdate(),?)");
			pstmt.setInt(1, newQuest.getSid());
			pstmt.setString(2, newQuest.getTitle());
			pstmt.setString(3, newQuest.getContent());
			pstmt.setInt(4, newQuest.getUid());
			pstmt.setTimestamp(5, newQuest.getExpiredTime());
			pstmt.executeUpdate();
			pstmt = con
					.prepareStatement("select questId from quest where sid=? and title=? and content=? and uid=? ");
			pstmt.setInt(1, newQuest.getSid());
			pstmt.setString(2, newQuest.getTitle());
			pstmt.setString(3, newQuest.getContent());
			pstmt.setInt(4, newQuest.getUid());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				qid = rs.getInt("questId");
			}
			AnswerDao dao = new AnswerDao();
			dao.insertAnswerAllMember(newQuest.getSid(), qid);

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// quest 수정
	public void updateQuestInfo(Quest updateQuest) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update quest set title=?, content=?, updatedTime=sysdate(), expiredTime=? where questId=?");
			pstmt.setString(1, updateQuest.getTitle());
			pstmt.setString(2, updateQuest.getContent());
			pstmt.setTimestamp(3, updateQuest.getExpiredTime());
			pstmt.setInt(4, updateQuest.getQuestId());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}